package pckDao;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import pckEntidad.Cliente;
import pckEntidad.Venta;
import pckUtil.Conexion;

public class VentaDao implements Serializable {

    private Conexion oConexion=new Conexion();
    private Venta oVenta;
    private List<Venta> lisVenta=null;
    private List<Venta> busVentaFech=null;
    private List<Venta> busVentaCom=null;
    private List<Venta> busVentaEst=null;

   public Venta obtener(String codigo) {
         String sql = "select * from tblVenta where idVent=?";
        try {
            PreparedStatement pst = oConexion.getConnection().prepareStatement(sql);                
            pst.setString(1, codigo);
            ResultSet rst = pst.executeQuery();
            
            while(rst.next()){
                oVenta=new Venta();
                oVenta.setIdvent(rst.getString(1));
                oVenta.setCliente(new ClienteDao().obtener(rst.getString(2)));
                oVenta.setTotalven(rst.getDouble(3));
                oVenta.setIgvven(rst.getDouble(4));
                oVenta.setFechven(rst.getString(5));
                oVenta.setTipocom(rst.getString(6));
                oVenta.setEstaven(rst.getString(7));
                oVenta.setImpoven(rst.getDouble(8));
                oVenta.setTrabajador(new TrabajadorDao().obtener(rst.getString(9)));
            }
            return  oVenta;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
         return null;
     }
   
    public void insertar(Venta oVen){
         String sql = "{call sping_Vent(?,?,?,?,?,?,?,?)}";
         CallableStatement cst;
        try {
            cst = oConexion.getConnection().prepareCall(sql);
            cst.setString(1, oVen.getCliente().getIdclie());
            cst.setDouble(2, oVen.getTotalven());
            cst.setDouble(3, oVen.getIgvven());
            cst.setString(4, oVen.getFechven());
            cst.setString(5, oVen.getTipocom());
            cst.setString(6, oVen.getEstaven());
            cst.setDouble(7, oVen.getImpoven());
            cst.setString(8, oVen.getTrabajador().getIdtrab());
            cst.execute();            
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
           
     }
    
            
    public List<Venta> listar(){
    String sql = "{call splis_Venta}";
        try {
            CallableStatement cst = oConexion.getConnection().prepareCall(sql);
            ResultSet rst = cst.executeQuery();
            lisVenta= new ArrayList<Venta>();
            while(rst.next()){
                oVenta = new Venta();
                oVenta.setIdvent(rst.getString(1)); 
                oVenta.setCliente(new ClienteDao().obtener(rst.getString(2)));
                oVenta.setTotalven(rst.getDouble(3));
                oVenta.setIgvven(rst.getDouble(4));
                oVenta.setFechven(rst.getString(5));
                oVenta.setTipocom(rst.getString(6));
                oVenta.setEstaven((rst.getString(7)));
                oVenta.setImpoven(rst.getDouble(8));
                oVenta.setTrabajador(new TrabajadorDao().obtener(rst.getString(9)));
                lisVenta.add(oVenta);
            }
            return  lisVenta;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
         return null;
     }
    
    
    public void actualizar( Venta oVen){
        try {
            String sql = "{call spact_tblVenta(?,?,?,?,?,?,?,?,?)}";
            CallableStatement cst = oConexion.getConnection().prepareCall(sql);
            cst.setString(1, oVen.getIdvent());
            cst.setString(2, oVen.getCliente().getIdclie());
            cst.setDouble(3, oVen.getTotalven());
            cst.setDouble(4, oVen.getIgvven());
            cst.setString(5, oVen.getFechven());
            cst.setString(6, oVen.getTipocom());
            cst.setString(7, oVen.getEstaven());
            cst.setDouble(8, oVen.getImpoven());
            cst.setString(9, oVen.getTrabajador().getIdtrab());
            cst.execute();
            
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
         
     }
    
    public void desactivar(String idVen){
    try{
        String sql="{call spcan_venta(?)}";
        CallableStatement cst=oConexion.getConnection().prepareCall(sql);
        cst.setString(1,idVen);
        cst.execute();
    }catch(SQLException ex){
        JOptionPane.showMessageDialog(null, ex.getMessage());
    }
         
    }
    
     
    public List<Venta> buscarFecha(String fecha){
     
           String sql = "{call spbus_ventaFec(?)}";
        try {
            CallableStatement cst = oConexion.getConnection().prepareCall(sql); 
            cst.setString(1, fecha);
            ResultSet rst = cst.executeQuery();
            busVentaFech= new ArrayList<Venta>();

            boolean econtrado=false;
            while(rst.next()){
                oVenta = new Venta();
                oVenta.setIdvent(cst.getString(1));
                oVenta.setCliente(new ClienteDao().obtener(cst.getString(2)));
                oVenta.setTotalven(cst.getDouble(3));
                oVenta.setIgvven(cst.getDouble(4));
                oVenta.setFechven(cst.getString(5));
                oVenta.setTipocom(cst.getString(6));
                oVenta.setEstaven(cst.getString(7));
                oVenta.setImpoven(cst.getDouble(8));
                oVenta.setTrabajador(new TrabajadorDao().obtener(cst.getString(9)));
                busVentaFech.add(oVenta);
                econtrado=true;
            }
            if (!econtrado) {
                JOptionPane.showMessageDialog(null, "No se registraron ventas para la fecha : "+fecha);
            }
            return  busVentaFech;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
         return null;
     }
    
  public List<Venta> buscarComp(String comp){
     
           String sql = "{call spbus_ventaCom(?)}";
        try {
            CallableStatement cst = oConexion.getConnection().prepareCall(sql); 
            cst.setString(1, comp);
            ResultSet rst = cst.executeQuery();
            busVentaCom= new ArrayList<Venta>();

            boolean econtrado=false;
            while(rst.next()){
                oVenta = new Venta();
                oVenta.setIdvent(cst.getString(1));
                oVenta.setCliente(new ClienteDao().obtener(cst.getString(2)));
                oVenta.setTotalven(cst.getDouble(3));
                oVenta.setIgvven(cst.getDouble(4));
                oVenta.setFechven(cst.getString(5));
                oVenta.setTipocom(cst.getString(6));
                oVenta.setEstaven(cst.getString(7));
                oVenta.setImpoven(cst.getDouble(8));
                oVenta.setTrabajador(new TrabajadorDao().obtener(cst.getString(9)));
                busVentaCom.add(oVenta);
                econtrado=true;
            }
            if (!econtrado) {
                JOptionPane.showMessageDialog(null, "No se registraron ventas para el comprobante : "+comp);
            }
            return  busVentaCom;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
         return null;
     }
    
 public List<Venta> buscarEst(String estado){
     
           String sql = "{call spbus_ventaEst(?)}";
        try {
            CallableStatement cst = oConexion.getConnection().prepareCall(sql); 
            cst.setString(1, estado);
            ResultSet rst = cst.executeQuery();
            busVentaEst= new ArrayList<Venta>();

            boolean econtrado=false;
            while(rst.next()){
                oVenta = new Venta();
                oVenta.setIdvent(cst.getString(1));
                oVenta.setCliente(new ClienteDao().obtener(cst.getString(2)));
                oVenta.setTotalven(cst.getDouble(3));
                oVenta.setIgvven(cst.getDouble(4));
                oVenta.setFechven(cst.getString(5));
                oVenta.setTipocom(cst.getString(6));
                oVenta.setEstaven(cst.getString(7));
                oVenta.setImpoven(cst.getDouble(8));
                oVenta.setTrabajador(new TrabajadorDao().obtener(cst.getString(9)));
                busVentaEst.add(oVenta);
                econtrado=true;
            }
            if (!econtrado) {
                JOptionPane.showMessageDialog(null, "No se registraron ventas ");
            }
            return  busVentaEst;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
         return null;
     }
    
}
